package com.aierkeji.call.b2c.dao.jpa.custom.ipml;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.orm.hibernate5.HibernateTemplate;

import com.aierkeji.call.b2c.contants.Contants;
import com.aierkeji.call.b2c.dao.jpa.custom.AkjCallClueDaoCustom;
import com.aierkeji.call.b2c.entity.AkjCallClueVo;
import com.aierkeji.call.b2c.entity.AkjCallUser;
import com.aierkeji.call.b2c.entity.PageBean;
import com.aierkeji.call.b2c.util.DateTimeUtils;
import com.aierkeji.call.b2c.util.StringUtils;


public class AkjCallClueDaoCustomImpl implements AkjCallClueDaoCustom{

	@Autowired
	HibernateTemplate hibernateTemplate;
	
	@Autowired
	JdbcTemplate jdbcTemplate;
	
	@Override
	public PageBean<AkjCallClueVo> findCallClueByAssignedToAndAssignedAtForPage(AkjCallUser user, Map<String,Object> map ,int pageSize) {
		
		PageBean<AkjCallClueVo> pageBean = new PageBean<AkjCallClueVo>();
		int page				 	= Integer.valueOf(map.get("page").toString());
		String nowDateStr 			= (String)map.get("updateAt");
		String lastCallTimeStar		= (String)map.get("lastCallTimeStar");
		String lastCallTimeEnd		= (String)map.get("lastCallTimeEnd");
		String name 				= (String)map.get("name");
		String phone				= (String)map.get("phone");
		String isCall 				= (String)map.get("isCall");
		String lastCallStatusStr 	= (String)map.get("lastCallStatus");
		String lastCallResultStr 	= (String)map.get("lastCallResult");
		String orderBySql			= "";
			
		if(StringUtils.isEmpty(nowDateStr)) {
			nowDateStr =DateTimeUtils.converDateToString( DateTimeUtils.getCurrentTime(),DateTimeUtils.DATE_PATTERN_DAY);
		}
		String initTodayStr = nowDateStr+" 00:00:00";
		String finish	 = nowDateStr+" 23:59:59";

		if (page <= 0){
            throw new RuntimeException("当前页数必须大于1");
        }
        if (pageSize <= 0){
            throw new RuntimeException("每页大小必须大于1");
        }
        
        String countSql = "select count(*) from akj_call_clue t where 1 = 1 and "
				+ "t.assigned_to = ? and t.assigned_at BETWEEN ? and ?" ;
        
        String sql = "select t.id, t.gender, t.name, t.phone, t.last_call_time, t.last_call_status, t.last_call_result,t.last_remark ,t.last_call_at,"
        		+ " t.clue_group_id,  t.city "
        		+ "from akj_call_clue t where 1 = 1 and "
				+ "t.assigned_to = ? and t.assigned_at BETWEEN ? and ? " ;
        
        List <Object> queryList 		= new  ArrayList<Object>();
		queryList.add(user.getId());
		queryList.add(initTodayStr);
		queryList.add(finish);
        
        //查询是否未拨打
        if(Contants.IS_CALL.equals(isCall)){
			sql 		+= " and t.last_call_at is not null ";
			countSql 	+= " and t.last_call_at is not null";
			orderBySql 	 = " order by t.last_call_at desc ";
		}else {
			sql 		+= " and t.last_call_at is null";
			countSql 	+= " and t.last_call_at is null";
			orderBySql 	 = " order by t.assigned_at desc ";
		}
        
        if(StringUtils.isNotEmpty(lastCallTimeStar)) {
        	sql 		+= " and t.last_call_time >= ?";
        	countSql 	+= " and t.last_call_time >= ?";
        	queryList.add(lastCallTimeStar);
        }
        if(StringUtils.isNotEmpty(lastCallTimeEnd)) {
        	sql 		+= " and t.last_call_time < ?";
        	countSql 	+= " and t.last_call_time < ?";
        	queryList.add(lastCallTimeEnd);
        }
        
        if(StringUtils.isNotEmpty(name)) {
        	sql 		+= " and t.name = ?";
        	countSql 	+= " and t.name = ?";
        	queryList.add(name);
        }
        if(StringUtils.isNotEmpty(phone)) {
        	sql 		+= " and t.phone = ?";
        	countSql 	+= " and t.phone = ?";
        	queryList.add(phone);
        }

        if(StringUtils.isNotEmpty(lastCallStatusStr)) {
        	sql 		+= " and t.last_call_status = ?";
        	countSql 	+= " and t.last_call_status = ?";
        	queryList.add(lastCallStatusStr);
        }
        if(StringUtils.isNotEmpty(lastCallResultStr)) {
        	sql 		+= " and t.last_call_result = ?";
        	countSql 	+= " and t.last_call_status = ?";
        	queryList.add(lastCallResultStr);
        }
        
  
        int totalSize = jdbcTemplate.queryForObject(countSql,queryList.toArray(),Integer.class);
        
        if (totalSize == 0){
        	pageBean.setData(null);
        	return pageBean;
        }
        
        //总页数
        int totalPage = totalSize%pageSize == 0 ? totalSize/pageSize : totalSize/pageSize + 1;
        //开始位置
        int offset = (page -1)*pageSize;
        //return item size
        int limit =  pageSize;
       
        sql += orderBySql;
        sql += " limit "+ limit +" offset "+offset;
       
    	List<AkjCallClueVo> akjCallClueList = jdbcTemplate.query(sql, new RowMapper<AkjCallClueVo>() {  
		    public AkjCallClueVo mapRow(ResultSet rs, int rowNum) throws SQLException {  
		    AkjCallClueVo akjCallClueVo = new AkjCallClueVo();  
		    akjCallClueVo.setId(rs.getLong("id"));
		    akjCallClueVo.setGender(rs.getString("gender"));
		    akjCallClueVo.setName(rs.getString("name"));
		    akjCallClueVo.setPhone(rs.getString("phone"));
		    akjCallClueVo.setLastCallTime(rs.getLong("last_call_time"));
		    akjCallClueVo.setLastRemark(rs.getString("last_remark"));
		    //akjCallClueVo.setLastCallStatus(CallStatus.valueOf(rs.getString("last_call_status")));
		    //akjCallClueVo.setLastCallResult(CallResult.valueOf(rs.getString("last_call_result")));
		    akjCallClueVo.setLastCallAt(rs.getDate("last_call_at"));
		    akjCallClueVo.setGroupId(rs.getLong("clue_group_id"));
		    akjCallClueVo.setLastCallStatus(rs.getString("last_call_status") == null ? "" : rs.getString("last_call_status"));
		    akjCallClueVo.setLastCallResult(rs.getString("last_call_result") == null ? "" : rs.getString("last_call_result"));
		    akjCallClueVo.setCity(rs.getString("city") == null ? "" : rs.getString("city"));
		    return akjCallClueVo;  
		    }  
		  },queryList.toArray());  
    	
    	
    	pageBean.setData(akjCallClueList);
    	pageBean.setElementTotalSize(akjCallClueList.size());
    	pageBean.setTotalSize(totalSize);
    	pageBean.setTotalPage(totalPage);
    	pageBean.setPage(page);
    	pageBean.setSize(pageSize);
        return pageBean;

		
//		String sql = "select t.name, t.phone from akj_call_clue t where 1 = 1 and "
//				+ "t.assigned_to = ? and (t.assigned_at >=? and t.assigned_at < ?)" ;
//		
//		List<AkjCallClueVo> akjCallClueList = jdbcTemplate.query(sql, new RowMapper<AkjCallClueVo>() {  
//				    public AkjCallClueVo mapRow(ResultSet rs, int rowNum) throws SQLException {  
//				    AkjCallClueVo akjCallClue = new AkjCallClueVo();  
//				    akjCallClue.setGender(Gender.valueOf(rs.getString("gender")));
//				    akjCallClue.setName(rs.getString("name"));
//				    akjCallClue.setPhone(rs.getString("phone"));
//				    return akjCallClue;  
//				    }  
//				  },queryList.toArray());  
		
		
		//return null;
	}

	@Override
	public boolean updateByid(Map<String, Object> map) {
		
		Long userId 			= (Long)map.get("userId");
		String lastCallAt		= (String)map.get("callAt");
		String callClueId 		= (String)map.get("clueId");
		String lastCallStatus 	= (String)map.get("callStatus");
		String lastCallTime 	= (String)map.get("callTime");
		String lastCallResult 	= (String)map.get("callResult");
		String lastRemark 		= (String)map.get("callRemark");
		
		boolean flag = false;
		List<Object> updateList = new ArrayList<Object>();
		
		
		String sql = "update akj_call_clue set last_call_at=?, updated_at =? ,updated_by = ? ";
		

		updateList.add(lastCallAt);
		updateList.add(lastCallAt);
		updateList.add(userId);
		if(StringUtils.isNotEmpty(lastCallStatus)) {
			sql += ", last_call_status=?";
			updateList.add(lastCallStatus);
		}
		
		if(StringUtils.isNotEmpty(lastCallTime)) {
			sql += ", last_call_time=?";
			updateList.add(lastCallTime);
		}
		
		if(StringUtils.isNotEmpty(lastCallResult)) {
			sql += ", last_call_result=?";
			updateList.add(lastCallResult);
		}
		
		if(StringUtils.isNotEmpty(lastRemark)&&!"null".equals(lastRemark)) {
			sql += ", last_remark=?";
			updateList.add(lastRemark);
		}else {
			sql += ", last_remark=?";
			updateList.add("");
		}
		
		sql += " where id = ?";
		updateList.add(callClueId);
		//防止重复上传通话记录
		if(!StringUtils.isEmpty(lastCallAt)) {
			Date lastCallDate = DateTimeUtils.converStringToDate(lastCallAt, DateTimeUtils.DATE_PATTERN_TIMESTAMP);
			if(lastCallDate!=null) {
				sql +=" and (last_call_at < ? or last_call_at is null)";
				updateList.add(lastCallDate);
			}
		}
	
		
		int count = jdbcTemplate.update(sql, updateList.toArray());
		
		if(count >0) {
			flag = true;
		}
		
		return flag;
	}

	@Override
	public List<AkjCallClueVo> findByGroupIdAndAssignedToAndAssignedAt(Long groupId,Long userId, Date nowDate) {
		
		String sql = "select t.clue_group_id, t.last_call_at, t.last_call_time, t.last_call_status, t.last_call_result from akj_call_clue"+	
					"where 1= =1 and t.clue_group_id = ? and t.assigned_to = ? and t.assigned_at BETWEEN ? and ?"+
					"order by t.last_call_at desc";
		
		String nowDateStr = DateTimeUtils.converDateToString(nowDate, DateTimeUtils.DATE_PATTERN_DAY);
		
		if(StringUtils.isEmpty(nowDateStr)) {
			nowDateStr =DateTimeUtils.converDateToString( DateTimeUtils.getCurrentTime(),DateTimeUtils.DATE_PATTERN_DAY);
		}
		String initTodayStr = nowDateStr+" 00:00:00";
		String finish	 = nowDateStr+" 23:59:59";
		
		List<Object> queryList = new ArrayList<Object>();
		queryList.add(groupId);
		queryList.add(userId);
		queryList.add(initTodayStr);
		queryList.add(finish);
		
		List<AkjCallClueVo> akjCallClueList = jdbcTemplate.query(sql, new RowMapper<AkjCallClueVo>() {  
		    public AkjCallClueVo mapRow(ResultSet rs, int rowNum) throws SQLException {  
		    AkjCallClueVo akjCallClueVo = new AkjCallClueVo();  
		    akjCallClueVo.setGroupId(rs.getLong("clue_group_id"));
		    akjCallClueVo.setLastCallTime(rs.getLong(rs.getString("last_call_time")));
		    akjCallClueVo.setLastCallAt(rs.getDate("last_call_at"));
		    akjCallClueVo.setLastCallStatus(rs.getString("last_call_status") == null ? "" : rs.getString("last_call_status"));
		    akjCallClueVo.setLastCallResult(rs.getString("last_call_result") == null ? "" : rs.getString("last_call_result"));
		    return akjCallClueVo;  
		    }  
		  },queryList.toArray());  
		
		
		return akjCallClueList;
	}

	
}
